#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys
import cgi
import json
import db_conf
import time
con = None

def post_compound_spectrum(cur, compound_id, spectrum_id):
    try:
        #INSERT INTO compound_spectrum (spectrum_id, compound_id, authentic) VALUES (3, 42624, 'Y') 
        cur.execute("""insert into compound_spectrum (spectrum_id, compound_id, authentic) values (%s, %s, %s)""", (spectrum_id, compound_id, 'Y')) 
    except psycopg2.DatabaseError, e:
        print 'Update Compound Spectrum Error %s' % e
        sys.exit(1)
 
def post_compound_synonym(cur, compound_id, synonyms, insert_flag):
    try:
        #clear all synonyms and reinsert
        if (insert_flag == 0):
            cur.execute("delete from compound_synonyms where compound_id = " + compound_id)
        #insert into compound_synonyms (synonym_text, compound_id) values ('test', 2464)
        synonyms = synonyms.split("\n")
        for s in synonyms:
            if (len(s)> 2):
                cur.execute("""insert into compound_synonyms (synonym_text, compound_id) values (%s, %s)""", (s, compound_id))
    except psycopg2.DatabaseError, e:
        print 'Update Compound Synonym Error %s' % e
        sys.exit(1)

def post_precursor(cur, precursor_id, product_id, reagent, mode):
    try:
        if (mode == "insert"):
            cur.execute("""insert into compound_derivatization (precursor_compound, product_compound, derivatization_reagent_id) values (%s, %s, %s)""", (precursor_id, product_id, reagent))
        else:
            cur.execute("""update compound_derivatization set precursor_compound=%s, derivatization_reagent_id=%s where product_compound = %s""", (precursor_id, reagent, product_id))				
    except psycopg2.DatabaseError, e:
        print 'Update Precursor Error %s' % e    
        sys.exit(1) 

try:
    #print time.asctime( time.localtime(time.time()) )	
    qform = cgi.FieldStorage()
    compound_name = qform.getvalue('compound_name')	    	
    sum_formula = qform.getvalue('sum_formula')
    nominal_mass = qform.getvalue('nominal_mass')
    molecular_weight = qform.getvalue('molecular_weight')
    monoisotopic_mass = qform.getvalue('monoisotopic_mass')
    reference_spectrum_id = qform.getvalue('reference_spectrum_id')
    ref_cas_id = qform.getvalue('ref_cas_id')
    ref_pubchem_id = qform.getvalue('ref_pubchem_id')
    ref_chemspider_id = qform.getvalue('ref_chemspider_id')
    ref_hmdb_id = qform.getvalue('ref_hmdb_id')
    ref_nist_id = qform.getvalue('ref_nist_id')
    ref_metlin_id = qform.getvalue('ref_metlin_id')
    ref_kegg_id = qform.getvalue('ref_kegg_id')
    checked = qform.getvalue('lcchecked')
    comments = qform.getvalue('lccomment')		
    inchi = qform.getvalue('inchi')
    inchi_key = qform.getvalue('inchi_key')
    reagent = qform.getvalue('derivation_reagent')
    precursor = qform.getvalue('compound_precursor')
    precursor_mode = qform.getvalue('precursor_mode')
    synonyms = qform.getvalue('compound_synonym')
    csyn_flag = qform.getvalue('csyn_flag')	
    compound_id = qform.getvalue('compound_id') 
    insert_flag = 0   
    if (compound_id == None or compound_id == ""):
        insert_flag = 1
        update_compound_sql = """INSERT INTO compound (compound_name,sum_formula,nominal_mass,molecular_weight,monoisotopic_mass,reference_spectrum_id,ref_cas_id,ref_pubchem_id,ref_hmdb_id,ref_nist_id,ref_metlin_id,user_comment,checked,inchi,inchi_key,ref_kegg_id, ref_chemspider_id) VALUES (%s,hillify(%s),%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) RETURNING compound_id"""
    else:
        update_compound_sql = """UPDATE compound set compound_name = %s, sum_formula = hillify(%s), nominal_mass = %s, molecular_weight=%s,monoisotopic_mass=%s,reference_spectrum_id=%s,ref_cas_id=%s,ref_pubchem_id=%s,ref_hmdb_id=%s,ref_nist_id=%s,ref_metlin_id=%s,user_comment=%s,checked=%s,inchi=%s,inchi_key=%s,ref_kegg_id=%s,ref_chemspider_id=%s where compound_id = %s"""
  	
    config = db_conf.getConfig('metabvis.conf')
    con = psycopg2.connect(host=db_conf.getDBHost(config), database=db_conf.getDBName(config), port=db_conf.getDBPort(config), user=db_conf.getUser(config), password=db_conf.getPassword(config)) 
    cur = con.cursor()
    if (compound_id == None or compound_id == ""):
    	cur.execute(update_compound_sql,(compound_name,sum_formula,nominal_mass,molecular_weight,monoisotopic_mass,reference_spectrum_id,ref_cas_id,ref_pubchem_id,ref_hmdb_id,ref_nist_id,ref_metlin_id,comments,checked,inchi,inchi_key,ref_kegg_id,ref_chemspider_id))
        compound_id = cur.fetchone()[0]
    else:
        cur.execute(update_compound_sql,(compound_name,sum_formula,nominal_mass,molecular_weight,monoisotopic_mass,reference_spectrum_id,ref_cas_id,ref_pubchem_id,ref_hmdb_id,ref_nist_id,ref_metlin_id,comments,checked,inchi,inchi_key,ref_kegg_id,ref_chemspider_id,compound_id))	

    #repopulate synonyms on flag mode
    if (csyn_flag == "changed"):
        post_compound_synonym(cur, compound_id, synonyms, insert_flag) 
    #need to update or insert compound_derivation
    if (reagent != None and reagent != "na"):
        post_precursor(cur, precursor, compound_id, reagent, precursor_mode)

    #if (reference_spectrum_id != None and len(reference_spectrum_id) > 0):
    #    post_compound_spectrum(cur, compound_id, reference_spectrum_id)	

    cur.execute("commit")
    print "Content-type: text/html;charset=utf-8\r\n"
    #print "post select - process result set " + time.asctime( time.localtime(time.time()) )
    pa = {}
    pa['_cname'] = compound_name
    pa['_cid'] = compound_id
    print json.dumps(pa)
except psycopg2.DatabaseError, e:
    print 'Update Error %s' % e    
    sys.exit(1)    
finally:
    if con:
        con.close()
